package com.springapp.dao.dao.impl;

import com.springapp.dao.dao.IStockTotalDAO;
import com.springapp.model.Article;
import com.springapp.model.InfoArticle;
import com.springapp.model.Palette;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by nicol_000 on 10/08/14.
 */
@Repository
public class StockServiceDAO implements IStockTotalDAO{

    @Qualifier("dataSource") //Fait référence au datasource dans le mvc-dispatcher.xml
    @Autowired
    private DataSource dataSource;


    @Override
    public List<Article> getLstPaletteEnStockTotal(boolean _bEstPicking) {
        String req;

        int iPick;
        if(_bEstPicking)
            iPick = 1;
        else
            iPick = 0;

        req =   "SELECT art.IDArticle\n" +
                "    , RefArticle\n" +
                "\t, artFou.IDFournisseur\n" +
                "    , artSCa.Libelle\n" +
                "\t, fou.NomFournisseur as 'Provenance'\n" +
                "\t, (emp.qteActuelleZoneStock * pArtFou.QteArticle) as 'QteArticle'\n" +
                "\t, dbo.getTotalByArticle(art.IDArticle, "+iPick+") as 'Total_Articles'\n" +
                "\t, emp.qteActuelleZoneStock as 'QtePalette'\n" +
                "\t, dbo.getTotalPaletteByArticle(art.IDArticle,"+iPick+") as 'Total_Palettes'\n" +
                "\t, p.EstHeterogene\n" +
                "FROM ARTICLE art\n" +
                "INNER JOIN Art_Classe artC ON artC.IDClasse = art.IDArtClasse\n" +
                "INNER JOIN Art_SousCategorie artSCa ON artSCa.IDSousCat = art.IDArt_SousCategorie\n" +
                "INNER JOIN Art_Fournisseur artFou ON artFou.IDArticle = art.IDArticle\n" +
                "INNER JOIN Fournisseur fou ON fou.IDFournisseur = artFou.IDFournisseur\n" +
                "INNER JOIN Pal_ArtFou pArtFou ON pArtFou.IDArtFou = artFou.IDArtFournisseur\n" +
                "INNER JOIN Palette p ON p.IDPalette = pArtFou.IDPalette\n" +
                "INNER JOIN Emplacement emp ON emp.IDEmplacement = p.IDEmplacement\n" +
                "WHERE p.IDEtatOperation <> 5\n" +
                "AND emp.EstPicking = "+iPick+"\n" +
                "AND p.EstHeterogene = 0 \n" +
                "AND emp.IDEmplacement <> 999999\n" +
                "Group by art.IDArticle, RefArticle, art.libelle, emp.EstPicking, artSCa.Libelle, fou.NomFournisseur, artFou.IDFournisseur, emp.qteActuelleZoneStock, pArtFou.QteArticle, p.EstHeterogene";


        Connection conn=null;
        ArrayList<Article> lstPaletteEnStocks =new ArrayList<Article>();


        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(req);
            ResultSet result=ps.executeQuery();
            while(result.next()){


                Article monArticle = new Article(
                        result.getInt("IDArticle"),
                        result.getString("RefArticle"),
                        result.getString("Libelle"),
                        result.getInt("Total_Palettes"),
                        result.getInt("Total_Articles"),
                        getInfoConditionnementArticle(result.getInt("IDArticle"), result.getInt("IDFournisseur"), conn),
                        result.getString("Provenance"),
                        result.getInt("QteArticle"),
                        result.getInt("QtePalette")
                );
                lstPaletteEnStocks.add(monArticle);
            }
            result.close();
            ps.close();


        } catch (SQLException e) {
            e.printStackTrace();
        }

        return lstPaletteEnStocks;
    }

    public List<InfoArticle> getInfoConditionnementArticle(int idArticle,int idFournisseur, Connection _conn) throws SQLException {
        String req = "Select  cond.libelle as 'TypeConditionnement'\n" +
                            " , artCond.Unite as 'Unite'\n" +
                            " , Poids as 'Poids'\n" +
                     "FROM Art_Conditionnement artcond\n" +
                     "inner join Conditionnement cond on cond.IDConditionnement = artcond.IDConditionnement\n" +
                     "WHERE artcond.IDArticle = "+idArticle+" AND artcond.IDFournisseur = "+idFournisseur+"\n" +
                     "Order by artcond.IDArticle, artcond.IDArtFournisseur";

        ArrayList<InfoArticle> lstInfoPalette = new ArrayList<InfoArticle>();

        PreparedStatement ps1 = _conn.prepareStatement(req);
        ResultSet resultats=ps1.executeQuery();
        while(resultats.next()){
                InfoArticle monInfoPalette = new InfoArticle(
                        resultats.getString("TypeConditionnement"),
                        resultats.getInt("Unite"),
                        resultats.getInt("Poids")
                );

                lstInfoPalette.add(monInfoPalette);
        }
        resultats.close();
        ps1.close();

        return lstInfoPalette;
    }
}
